# -*- coding:utf-8 -*-
# @Time   :  19:42
# @Author : jinzhongyi
# @File   : ExcelProcess_ver2.py
import xlwings as xw
import numpy as np
import datetime
import calendar
from collections import OrderedDict
import requests
import xlrd
import openpyxl
'''
此process适用于从PLMP中导出的排期文档，对应金山文档中“贸易融资团队行员排期”表
'''

def read_online_excel(excel):

    sheet = excel.sheet_by_name('Sheet1')

    row_count = sheet.nrows  # 行数
    col_count = sheet.ncols  # 列数
    print('%s表格大小为：%d行, %d列' % ('贸易融资团队行员排期', row_count, col_count))

    sht_all = []
    for i in range(row_count): # 读取一个sheet所有的内容
        value = sheet.row_values(i, 0, col_count)
        sht_all.append(value)
    sht_all = np.asarray(sht_all)
    return sht_all


'''
gen_project_dict函数：
    输入：dict_key, projects, time_to_str, sht_all
    输出：projects的字典，字典结构为{'项目名称',{'日期', list[任务1, 任务2, ....]} }
    功能：用来辅助project_classification
'''
def gen_project_dict(dict_key, projects, time_to_str, sht_all, j):
    if dict_key not in projects.keys():
        dict_value = {}
        dict_value[time_to_str] = []
        dict_value[time_to_str].append(sht_all[0, j])
    else:
        dict_value = projects[dict_key]  # 取出dict_value
        # 判断符合条件的日期是否已经在dict_value中了
        if time_to_str not in dict_value.keys():
            # 不存在，新建并填充数据
            dict_value[time_to_str] = []
            dict_value[time_to_str].append(sht_all[0, j])
        else:
            dict_value[time_to_str].append(sht_all[0, j])
    projects[dict_key] = dict_value
    return projects


'''
project_classification(sht_all, today)
    输入：sht_all表格所有数据；today当天日期，格式为datetime格式(例：2020-09-02 00:00:00)
    输出：upcoming_projects字典，即将开始的项目； endcoming_projects字典，即将结束的项目
'''


def project_classification(sht_all, today):
    upcoming_projects = {}  # 即将开始的项目，其中key:项目+日期  value：一个任务list
    project_manager={} # 用来保存项目和排期负责人的对应关系
    # endcoming_projects = {}  # 即将结束的项目，与上述相同
    # today = datetime.datetime(2020, 6, 1)  # 测试用今日时间

    for i in range(sht_all.shape[0]):
        project_manager[sht_all[i,0]] = sht_all[i,2] # 项目-排期负责人
        for j in range(sht_all.shape[1]):
            try:
                # 表格中还有项目名称等文字形式，无法转换成时间格式
                time = datetime.datetime.strptime(sht_all[i, j], '%Y/%m/%d')  # 2020-09-02 00:00:00
            except ValueError:
                pass
            else:
                time_to_str = time.strftime('%Y-%m-%d')  # 格式化时间，只保留年月日，2020-09-02
                if (0 <= ((time - today).days) <= 2):
                    # 如果时间差在[0, 2]之间，则是即将开始的任务，即提前三天提醒
                    # 判断是否存在项目
                    dict_key = sht_all[i, 0]
                    try:
                        sht_all[0, j]  # 防止越界，最后一项的时候就没有下一个开始的任务，而且一旦越界当然也没有dict_value
                    except:
                        pass
                    else:
                        upcoming_projects = gen_project_dict(dict_key, upcoming_projects, time_to_str, sht_all, j)

                # elif (-2 <= ((time - today).days) <= 0):  #
                #     # 如果时间差在[-2, 0)之间，则是即将开始的任务
                #     dict_key = sht_all[i, 0]
                #     try:
                #         sht_all[0, j]  # 防止越界，最后一项的时候就没有下一个开始的任务，而且一旦越界当然也没有dict_value
                #     except:
                #         pass
                #     else:
                #         upcoming_projects = gen_project_dict(dict_key, upcoming_projects, time_to_str, sht_all, j)

    return upcoming_projects, project_manager


'''
post_project_content(projects)
    输入：projects可以是upcoming_projects或者endcoming_projects
    输出：所需要发送的具体内容     
'''

# 构建即将开始下一阶段任务的content内容
def post_project_content(projects, project_manager):
    content = ''
    if len(projects) > 0:
        count = 0
        for key, value in projects.items():
            count += 1
            project_content = str(count) + '.' + key + r'\n'
            task_content = ''
            # print('==============================================')
            # # value是一个字典，key值为日期，按照key进行排序
            # print('未排序的value:', value)
            #
            # print('排序后的value:', OrderedDict(sorted(value.items(), key=lambda item:item[0])))
            # print('==============================================')
            value = OrderedDict(sorted(value.items(), key=lambda item:item[0]))
            for time, task in value.items():
                temp_str = ''
                # print(str(task))
                for i in range(len(task)):
                    if i == len(task)-1:
                        temp_str += task[i]
                    else:
                        temp_str += (task[i] + '、')
                task_content_unit = '>' + time + ' ' +r'<font color=\"info\">' + temp_str + r'</font>\n'
                task_content += task_content_unit
            content += ('>' + project_content + task_content + r'@' +project_manager[key] + r'\n')
    return content



'''
post_content(upcoming_contents, endcoming_contents)
    输入：upcoming_contents, endcoming_contents均为post_project_content的输出
    输出：传给微信webhook的content内容
'''
def post_content(upcoming_contents):
    headline = r'## 阶段提醒小助手\n'
    head1 = r'### 以下项目即将开始下一阶段\n'
    # head2 = r'### 以下项目即将到期\n'
    return headline + head1 + upcoming_contents

'''
post_action(url, post_data)
    输入：url为企业微信webhook地址，post_data为post_cotent的输出内容
    输出：成功输出Success，失败则输出Error
'''

def post_action(url, post_data):
    # post_data = '{"msgtype" : "markdown", "markdown" : {"content": "%s",' % post_data +'"mentioned_mobile_list": [%s]}}' % mentioned_mobile_list
    post_data = '{"msgtype" : "markdown", "markdown" : {"content": "%s"}},' % post_data
    print(post_data)


    if url == '':
        return 'Url is empty!'
    else:
        r = requests.post(url, data = post_data.encode())
        rstr = r.json()
        if r.status_code == 200 and 'error' not in rstr:
            result = 'Success'
            return result
        else:
            return 'Error'